package com.clw.demo

import org.apache.spark.sql.SparkSession
import org.apache.spark.{SparkConf, SparkContext}

object Demo1 {
  def main(args: Array[String]): Unit = {
    val conf = new SparkConf().setAppName("appName").setMaster("local[*]")
    val context = new SparkContext(conf)
//    context.setLogLevel("warn")
    val spark = SparkSession
      .builder()
      .appName("Spark SQL basic example")
      .config("spark.some.config.option", "some-value")
      .getOrCreate()
    val score = spark.read.option("delimiter", ",").schema("sid int,cid int,number int").csv("data/score_info.txt")
    val store = spark.read.option("delimiter", ",").option("header", "true").csv("data/storeInfo.txt")
    val student = spark.read.option("delimiter", ",").schema("sid int,sname string,birthday string,sex string,clasname string,nu double").csv("data/student_info.txt")
    val teacer = spark.read.option("delimiter", ",").schema("tid int,tname string").csv("data/teacher_info.txt")
    val course = spark.read.option("delimiter", ",").schema("cid int,cname string,tid int").csv("data/course_info.txt")
    score.createOrReplaceTempView("score")
    store.createOrReplaceTempView("store")
    student.createOrReplaceTempView("student")
    teacer.createOrReplaceTempView("teacher")
    course.createOrReplaceTempView("course")

//    5)查询出三门科目的平均成绩，并将其代课老师打印到控制台
    println("5)查询出三门科目的平均成绩，并将其代课老师打印到控制台")
    spark.sql(
      """
        |select tname,nuu from course a left join (select a.cid,avg(number) nuu from score a left join course b on a.cid = b.cid group by a.cid) b on a.cid = b. cid left join teacher c on a.tid = c.tid
        |""".stripMargin).show()
//    6)查询所有学生信息，将性别进行转换：男->man 女->women
    println(" 6)查询所有学生信息，将性别进行转换：男->man 女->women")
    spark.sql(
      """
        |select if(sex=='男','man','women') from student
        |""".stripMargin).show()
//      7)查询出有任意两门成绩低于60的学生信息
    println("7)查询出有任意两门成绩低于60的学生信息")
    spark.sql(
      """
        |select b.sid from student a left join score b on a.sid = b.sid where number<60 group by b.sid having count(*)>=2
        |""".stripMargin).show()
//    8)查询出语文成绩前3的学生信息及分数
    println("8)查询出语文成绩前3的学生信息及分数")
    spark.sql(
      """
        |select a.*,number from student a left join score b on a.sid = b.sid left join course c on b.cid = c.cid
        |where cname = '语文' order by number desc limit 3
        |""".stripMargin).show()
//    9)查询出男生三科总分的平均分以及女生三科总分的平均分
    println(")查询出男生三科总分的平均分以及女生三科总分的平均分")
    spark.sql(
      """
        |select a.sex,snu/(cn) from
        |(select sex,sum(number) snu from student a left join score b on a.sid = b.sid group by sex) a
        |left join (select sex,count(*) cn from student group by sex) b on a.sex = b.sex
        |""".stripMargin).show()
//    10)查询出哪位同学学过的科目数小于3
    println("10)查询出哪位同学学过的科目数小于3")
    spark.sql(
      """
        |select a.*,b.* from student a inner join (select sid,count(*) cn from score group by sid having count(*) < 3) b on a.sid = b.sid
        |""".stripMargin).show()
//    11）查询和编号02教师同姓的同学信息以及总分
    println("11）查询和编号02教师同姓的同学信息以及总分")
    spark.sql(
      """
        |select * from student a inner join (select b.sid,sum(number) from student a left join score b on a.sid = b.sid inner join (select a.sid from student a left join score b on a.sid = b.sid
        |where sname like concat((select substring(tname,0,1) from teacher where tid = 1002),'%') group by a.sid) c on b.sid = c.sid
        |group by b.sid ) b on a.sid = b.sid
        |""".stripMargin).show()
//    12）查询编号03的教师所交科目中有哪些学生本科成绩小于60分
    println("12）查询编号03的教师所交科目中有哪些学生本科成绩小于60分")
    spark.sql(
      """
        |select * from student a left join score b on a.sid = b.sid left join course c on b.cid = c.cid left join teacher d on c.tid = d.tid
        |where c.tid = 1003 and number<60
        |""".stripMargin).show()
  }
}
